-- if you modify this schema, be sure to update the reference db file in this directory AND regenerate any existing databases
PRAGMA page_size=4096;
PRAGMA foreign_keys = ON;

DROP VIEW IF EXISTS vw_aspnet_Applications;
DROP VIEW IF EXISTS vw_aspnet_MembershipUsers;
DROP VIEW IF EXISTS vw_aspnet_Profiles;
DROP VIEW IF EXISTS vw_aspnet_Roles;
DROP VIEW IF EXISTS vw_aspnet_Users;
DROP VIEW IF EXISTS vw_aspnet_UsersInRoles;
DROP VIEW IF EXISTS vw_aspnet_WebPartState_Paths;
DROP VIEW IF EXISTS vw_aspnet_WebPartState_Shared;
DROP VIEW IF EXISTS vw_aspnet_WebPartState_User;
DROP VIEW IF EXISTS vw_aspnet_UsersMerged;
DROP VIEW IF EXISTS vw_aspnet_UsersInRolesMerged;
DROP VIEW IF EXISTS vw_aspnet_UserProfileMerged;
DROP VIEW IF EXISTS vw_aspnet_SiteMap;

DROP TABLE IF EXISTS aspnet_Applications;
DROP TABLE IF EXISTS aspnet_Membership;
DROP TABLE IF EXISTS aspnet_Paths;
DROP TABLE IF EXISTS aspnet_PersonalizationAllUsers;
DROP TABLE IF EXISTS aspnet_PersonalizationPerUser;
DROP TABLE IF EXISTS aspnet_Profile;
DROP TABLE IF EXISTS aspnet_Roles;
DROP TABLE IF EXISTS aspnet_Users;
DROP TABLE IF EXISTS aspnet_UsersInRoles;
DROP TABLE IF EXISTS aspnet_WebEvent_Events;
DROP TABLE IF EXISTS aspnet_SiteMap;


CREATE TABLE aspnet_Applications (
	ApplicationName nvarchar(256) NOT NULL UNIQUE,
	LoweredApplicationName nvarchar(256) NOT NULL UNIQUE,
	ApplicationId guid PRIMARY KEY NOT NULL,
	Description nvarchar(256)
);

CREATE TABLE aspnet_Membership (
	ApplicationId guid NOT NULL,
	UserId guid PRIMARY KEY NOT NULL,
	Password nvarchar(128) NOT NULL,
	PasswordFormat int NOT NULL DEFAULT 0,
	PasswordSalt nvarchar(128) NOT NULL,
	MobilePIN nvarchar(16),
	Email nvarchar(256),
	LoweredEmail nvarchar(256),
	PasswordQuestion nvarchar(256),
	PasswordAnswer nvarchar(128),
	IsApproved bit NOT NULL,
	IsLockedOut bit NOT NULL,
	CreateDate datetime NOT NULL,
	LastLoginDate datetime NOT NULL,
	LastPasswordChangedDate datetime NOT NULL,
	LastLockoutDate datetime NOT NULL,
	FailedPasswordAttemptCount int NOT NULL,
	FailedPasswordAttemptWindowStart datetime NOT NULL,
	FailedPasswordAnswerAttemptCount int NOT NULL,
	FailedPasswordAnswerAttemptWindowStart datetime NOT NULL,
	Comment ntext,
	CONSTRAINT FK_aspnet_Membership_ApplicationId_aspnet_Applications_ApplicationId 
		FOREIGN KEY (ApplicationId) 
		REFERENCES aspnet_Applications (ApplicationId),
	CONSTRAINT FK_aspnet_Membership_UserId_aspnet_Users_UserId 
		FOREIGN KEY (UserId) 
		REFERENCES aspnet_Users (UserId)
);

 
CREATE TABLE aspnet_SiteMap (
	ApplicationId guid NOT NULL,
	NodeId nvarchar(50) PRIMARY KEY NOT NULL,
	Title nvarchar(50),
	Description nvarchar (512),
	Url nvarchar (512),
	Roles ntext,
	ParentNodeId nvarchar(50),
	ImplicitResourceKey nvarchar(50),
	ExplicitResourceKeys ntext,
	Attributes ntext,
	CONSTRAINT FK_aspnet_SiteMap_ParentNodeId_aspnet_SiteMap_NodeId
		FOREIGN KEY (ParentNodeId) 
		REFERENCES aspnet_SiteMap (NodeId) ON DELETE CASCADE
);


CREATE TABLE aspnet_Paths (
	ApplicationId guid NOT NULL,
	PathId guid PRIMARY KEY NOT NULL,
	Path nvarchar(256) NOT NULL,
	LoweredPath nvarchar(256) NOT NULL,
	CONSTRAINT FK_aspnet_Paths_ApplicationId_aspnet_Applications_ApplicationId 
		FOREIGN KEY (ApplicationId) 
		REFERENCES aspnet_Applications (ApplicationId)
);

CREATE TABLE aspnet_PersonalizationAllUsers (
	PathId guid PRIMARY KEY NOT NULL,
	PageSettings image NOT NULL,
	LastUpdatedDate datetime NOT NULL,
	CONSTRAINT FK_aspnet_PersonalizationAllUsers_PathId_aspnet_Paths_PathId 
		FOREIGN KEY (PathId) 
		REFERENCES aspnet_Paths (PathId)
);

CREATE TABLE aspnet_PersonalizationPerUser (
	Id guid PRIMARY KEY NOT NULL,
	PathId guid ,
	UserId guid ,
	PageSettings image NOT NULL,
	LastUpdatedDate datetime NOT NULL,
	CONSTRAINT FK_aspnet_PersonalizationPerUser_PathId_aspnet_Paths_PathId 
		FOREIGN KEY (PathId) 
		REFERENCES aspnet_Paths (PathId),
	CONSTRAINT FK_aspnet_PersonalizationPerUser_UserId_aspnet_Users_UserId 
		FOREIGN KEY (UserId) 
		REFERENCES aspnet_Users (UserId)
);

CREATE TABLE aspnet_Profile (
	UserId guid PRIMARY KEY NOT NULL,
	PropertyNames ntext NOT NULL,
	PropertyValuesString ntext NOT NULL,
	PropertyValuesBinary image NOT NULL,
	LastUpdatedDate datetime NOT NULL,
	CONSTRAINT FK_aspnet_Profile_UserId_aspnet_Users_UserId 
		FOREIGN KEY (UserId) 
		REFERENCES aspnet_Users (UserId)
);

CREATE TABLE aspnet_Roles (
	ApplicationId guid NOT NULL,
	RoleId guid PRIMARY KEY NOT NULL,
	RoleName nvarchar(256) NOT NULL,
	LoweredRoleName nvarchar(256) NOT NULL,
	Description nvarchar(256),
	CONSTRAINT FK_aspnet_Roles_ApplicationId_aspnet_Applications_ApplicationId 
		FOREIGN KEY (ApplicationId) 
		REFERENCES aspnet_Applications (ApplicationId)
);


CREATE TABLE aspnet_Users (
	ApplicationId guid NOT NULL,
	UserId guid PRIMARY KEY NOT NULL,
	UserName nvarchar(256) NOT NULL,
	LoweredUserName nvarchar(256) NOT NULL,
	MobileAlias nvarchar(16) DEFAULT NULL,
	IsAnonymous bit NOT NULL DEFAULT 0,
	LastActivityDate datetime,
	CONSTRAINT FK_aspnet_Users_ApplicationId_aspnet_Applications_ApplicationId 
		FOREIGN KEY (ApplicationId) 
		REFERENCES aspnet_Applications (ApplicationId)
);

CREATE TABLE aspnet_UsersInRoles (
	UserId guid NOT NULL,
	RoleId guid NOT NULL,
	CONSTRAINT PK_aspnet_UsersInRoles 
		PRIMARY KEY (UserId, RoleId),
	CONSTRAINT FK_aspnet_UsersInRoles_RoleId_aspnet_Roles_RoleId 
		FOREIGN KEY (RoleId) 
		REFERENCES aspnet_Roles (RoleId),
	CONSTRAINT FK_aspnet_UsersInRoles_UserId_aspnet_Users_UserId 
		FOREIGN KEY (UserId) 
		REFERENCES aspnet_Users (UserId)
);

CREATE TABLE aspnet_WebEvent_Events (
	EventId char(32) PRIMARY KEY NOT NULL,
	EventTimeUtc datetime NOT NULL,
	EventTime datetime NOT NULL,
	EventType nvarchar(256) NOT NULL,
	EventSequence decimal(19, 0) NOT NULL,
	EventOccurrence decimal(19, 0) NOT NULL,
	EventCode int NOT NULL,
	EventDetailCode int NOT NULL,
	Message nvarchar(1024),
	ApplicationPath nvarchar(256),
	ApplicationVirtualPath nvarchar(256),
	MachineName nvarchar(256) NOT NULL,
	RequestUrl nvarchar(1024),
	ExceptionType nvarchar(256),
	Details ntext
);


CREATE VIEW vw_aspnet_Applications AS 
SELECT		ApplicationName, LoweredApplicationName, ApplicationId, Description
FROM		aspnet_Applications;

CREATE VIEW vw_aspnet_MembershipUsers AS 
SELECT		aspnet_Membership.UserId, aspnet_Membership.PasswordFormat, aspnet_Membership.MobilePIN, aspnet_Membership.Email, 
			aspnet_Membership.LoweredEmail, aspnet_Membership.PasswordQuestion, aspnet_Membership.PasswordAnswer, 
			aspnet_Membership.IsApproved, aspnet_Membership.IsLockedOut, aspnet_Membership.CreateDate, 
			aspnet_Membership.LastLoginDate, aspnet_Membership.LastPasswordChangedDate, aspnet_Membership.LastLockoutDate,
			aspnet_Membership.FailedPasswordAttemptCount, aspnet_Membership.FailedPasswordAttemptWindowStart,
			aspnet_Membership.FailedPasswordAnswerAttemptCount, aspnet_Membership.FailedPasswordAnswerAttemptWindowStart, 
			aspnet_Membership.Comment, aspnet_Users.ApplicationId, aspnet_Users.UserName, aspnet_Users.MobileAlias, 
			aspnet_Users.IsAnonymous, aspnet_Users.LastActivityDate
FROM		aspnet_Membership INNER JOIN
			aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId;

CREATE VIEW vw_aspnet_Profiles AS 
SELECT		UserId, LastUpdatedDate, LENGTH(PropertyNames) + LENGTH(PropertyValuesString) + LENGTH(PropertyValuesBinary) AS DataSize
FROM		aspnet_Profile;

CREATE VIEW vw_aspnet_Roles AS 
SELECT		ApplicationId, RoleId, RoleName, LoweredRoleName, Description
FROM		aspnet_Roles;

CREATE VIEW vw_aspnet_Users AS 
SELECT		ApplicationId, UserId, UserName, LoweredUserName, MobileAlias, IsAnonymous, LastActivityDate
FROM		aspnet_Users;

CREATE VIEW vw_aspnet_UsersInRoles AS 
SELECT		UserId, RoleId
FROM		aspnet_UsersInRoles;

CREATE VIEW vw_aspnet_WebPartState_Paths AS 
SELECT		ApplicationId, PathId, Path, LoweredPath
FROM		aspnet_Paths;

CREATE VIEW vw_aspnet_WebPartState_Shared AS 
SELECT		PathId, LENGTH(PageSettings) AS DataSize, LastUpdatedDate
FROM		aspnet_PersonalizationAllUsers;

CREATE VIEW vw_aspnet_WebPartState_User AS 
SELECT		Id, PathId, UserId, LENGTH(PageSettings) AS DataSize, LastUpdatedDate
FROM		aspnet_PersonalizationPerUser;

CREATE VIEW vw_aspnet_UsersMerged AS 
SELECT		aspnet_Membership.ApplicationId, aspnet_Membership.UserId, aspnet_Membership.Password, aspnet_Membership.PasswordFormat,
			aspnet_Membership.PasswordSalt, aspnet_Membership.MobilePIN, aspnet_Membership.Email, aspnet_Membership.LoweredEmail,
			aspnet_Membership.PasswordQuestion, aspnet_Membership.PasswordAnswer, aspnet_Membership.IsApproved, aspnet_Membership.IsLockedOut,
			aspnet_Membership.CreateDate, aspnet_Membership.LastLoginDate, aspnet_Membership.LastPasswordChangedDate, 
			aspnet_Membership.LastLockoutDate, aspnet_Membership.FailedPasswordAttemptCount, aspnet_Membership.FailedPasswordAttemptWindowStart,
			aspnet_Membership.FailedPasswordAnswerAttemptCount, aspnet_Membership.FailedPasswordAnswerAttemptWindowStart, aspnet_Membership.Comment,
			aspnet_Users.UserName, aspnet_Users.LoweredUserName, aspnet_Users.MobileAlias, aspnet_Users.IsAnonymous, aspnet_Users.LastActivityDate
FROM aspnet_Membership INNER JOIN
			aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId;


CREATE VIEW vw_aspnet_UsersInRolesMerged AS 
SELECT		aspnet_Roles.ApplicationId, aspnet_Roles.RoleId, aspnet_Roles.RoleName, aspnet_Roles.LoweredRoleName, aspnet_Roles.Description, 
			aspnet_Users.UserId, aspnet_Users.UserName, aspnet_Users.LoweredUserName, aspnet_Users.MobileAlias, aspnet_Users.IsAnonymous, 
			aspnet_Users.LastActivityDate
FROM        aspnet_Roles INNER JOIN
            aspnet_UsersInRoles ON aspnet_Roles.RoleId = aspnet_UsersInRoles.RoleId INNER JOIN
            aspnet_Users ON aspnet_UsersInRoles.UserId = aspnet_Users.UserId;

CREATE VIEW vw_aspnet_UserProfileMerged AS 
SELECT		aspnet_Users.ApplicationId, aspnet_Users.UserId, aspnet_Users.UserName, aspnet_Users.LoweredUserName, aspnet_Users.MobileAlias, 
			aspnet_Users.IsAnonymous, aspnet_Users.LastActivityDate, aspnet_Profile.PropertyNames, aspnet_Profile.PropertyValuesString, 
			aspnet_Profile.PropertyValuesBinary, aspnet_Profile.LastUpdatedDate, LENGTH(aspnet_Profile.PropertyNames) + LENGTH(aspnet_Profile.PropertyValuesString) + LENGTH(aspnet_Profile.PropertyValuesBinary) AS DataSize
FROM        aspnet_Profile INNER JOIN
            aspnet_Users ON aspnet_Profile.UserId = aspnet_Users.UserId;

CREATE VIEW vw_aspnet_SiteMap AS
SELECT		ApplicationId, NodeId, Title, Description, Url, Roles, ParentNodeId, ImplicitResourceKey, ExplicitResourceKeys, Attributes
FROM        aspnet_SiteMap;

CREATE UNIQUE INDEX IX_ApplicationName ON aspnet_Applications (ApplicationName);
CREATE UNIQUE INDEX IX_LoweredApplicationName ON aspnet_Applications (LoweredApplicationName);
CREATE INDEX aspnet_Applications_Index ON aspnet_Applications (LoweredApplicationName);
CREATE INDEX aspnet_Membership_index ON aspnet_Membership (ApplicationId, LoweredEmail);
CREATE UNIQUE INDEX aspnet_Paths_index ON aspnet_Paths (ApplicationId, LoweredPath);
CREATE UNIQUE INDEX aspnet_PersonalizationPerUser_index1 ON aspnet_PersonalizationPerUser (PathId, UserId);
CREATE UNIQUE INDEX aspnet_PersonalizationPerUser_ncindex2 ON aspnet_PersonalizationPerUser (UserId, PathId);
CREATE UNIQUE INDEX aspnet_Roles_index1 ON aspnet_Roles (ApplicationId, LoweredRoleName);
CREATE INDEX aspnet_UsersInRoles_index ON aspnet_UsersInRoles (RoleId);
CREATE UNIQUE INDEX aspnet_Users_Index ON aspnet_Users (ApplicationId, LoweredUserName);
CREATE INDEX aspnet_Users_Index2 ON aspnet_Users (ApplicationId, LastActivityDate);



